package com.tailyou.hms.firstapp.act;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;

import androidx.appcompat.app.AppCompatActivity;

import com.tailyou.hms.firstapp.R;

public class SQLiteActivity extends AppCompatActivity {

    private SQLiteDatabase db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqlite);
        findViewById(R.id.btnCreateDb).setOnClickListener(v -> createDb());
        findViewById(R.id.btnCreateTb).setOnClickListener(v -> createTb());
        findViewById(R.id.btnInsert).setOnClickListener(v -> insertData());
        findViewById(R.id.btnQuery).setOnClickListener(v -> queryData());
        findViewById(R.id.btnUpdateTb).setOnClickListener(v -> updateData());
        findViewById(R.id.btnDelete).setOnClickListener(v -> deleteData());
        findViewById(R.id.btnDrop).setOnClickListener(v -> dropTable());
    }

    /**
     * 删除表格
     */
    private void dropTable() {
        String sql = "drop table stu_table";
        //执行SQL
        db.execSQL(sql);
    }

    /**
     * 删除数据
     */
    private void deleteData() {
        //删除SQL语句
        String sql = "delete from stu_table where _id = 1";
        //执行SQL语句
        db.execSQL(sql);
    }

    /**
     * 更新数据
     */
    private void updateData() {
        //修改SQL语句
        String sql = "update stu_table set snumber = 654321 where _id = 1";
        //执行SQL
        db.execSQL(sql);
    }

    /**
     * 查询数据
     */
    private void queryData() {
        Cursor cursor = db.query("stu_table", null, null,
                null, null, null, null);
        while (cursor.moveToNext()) {
            int id = cursor.getInt(0);
            String stuName = cursor.getString(1);
            String stuNo = cursor.getString(2);
            Log.d("SQLite", id + "-" + stuName + "-" + stuNo);
        }
        cursor.close();
    }

    /**
     * 插入数据
     */
    private void insertData() {
        //插入数据SQL语句
        String sql_insert_data = "insert into stu_table(sname,snumber) values('xiaoming','01005')";
        //执行SQL语句
        db.execSQL(sql_insert_data);
    }

    /**
     * 创建数据表
     */
    private void createTb() {
        //创建表SQL语句
        String sql_create_table = "create table stu_table(_id integer primary key autoincrement,sname text,snumber text)";
        //执行SQL语句
        db.execSQL(sql_create_table);
    }

    /**
     * 打开/创建数据库
     */
    private void createDb() {
        db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.tailyou.hms.firstapp/stu.db", null);
    }

}